import sqlite3
import pandas as pd
from tkinter import ttk
import tkinter

class sql():
    def __init__(self,root,dass):
        root.title('SQLITE control')
        root.geometry('1800x850')

        self.S = tkinter.Scrollbar(root, orient=tkinter.HORIZONTAL)
        self.S2 = tkinter.Scrollbar(root, orient=tkinter.VERTICAL)

        self.S.pack(side=tkinter.BOTTOM, fill=tkinter.X)
        self.S2.pack(side=tkinter.RIGHT, fill=tkinter.Y)

        self.form = ttk.Treeview(root, height=40, xscrollcommand=self.S.set, yscrollcommand=self.S2.set)  # 创建表格对象
        self.form.pack()
        self.conn = sqlite3.connect(dass)
        self.cur = self.conn.cursor()

    def read(self):
        try:
            a.read2()
        except:
            a.create()
        finally:
            a.read2()

    def read2(self):
        df = pd.read_sql_query("SELECT * FROM ToolType", con=self.conn)
        self.form['columns'] = tuple(df.columns)

        for i in df.columns:
            self.form.column(i, width=200, anchor='center')
            self.form.heading(i, text=i)
        d = df.shape
        for i in range(d[0]):
            self.form.insert("", i, text=i, values=tuple(df.iloc[i]))
            # form.insert("", 0, text='1', values=("1", "djy", "18"))

        self.S.config(command=self.form.xview)
        self.S2.config(command=self.form.yview)

    def create(self):
        conn = sqlite3.connect('test.db')
        cur = conn.cursor()
        data = pd.read_csv("ToolType.csv")
        data.to_sql('ToolType', conn, if_exists='replace', index=False)
        cur.close()
        conn.close()



if __name__ == '__main__':
    root = tkinter.Tk()
    a = sql(root,"test.db")
    a.read()
    root.mainloop()
